/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.harding.comp431;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 *
 * @author fmccown and jdepena
 */
public class Database {
    
    private String jdbcConnectionString;
    
    public Database(String jdbcConnectionString) {
        this.jdbcConnectionString = jdbcConnectionString;
    }
            
    /**
     * Query the database for the given username and return all the info
     * in a SpitterUser. Returns null if the username was not found in the DB.
     * 
     * @param username
     * @return 
     */
    public SpitterUser getUser(String username) {
        
        String query = "SELECT password, about FROM SpitterUsers WHERE " +
                            "username = ?";
        
        try {
            Connection conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, username);
            ResultSet rs = statement.executeQuery();

            SpitterUser user = new SpitterUser();
            user.setUsername(username);

            rs.next();
            user.setPassword(rs.getString(1));
            user.setAbout(rs.getString(2));            

            conn.close();

            return user;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Error in the SQL: " + query, ex);
        }
        
        return null;
    }
   
    /**
     * Query the database for the given username and return all the speets
     * in a SpitterUser. Returns null if the username was not found in the DB.
     * 
     * @param username
     * @return 
     */
    public ArrayList<Speet> getAllSpeets(String username) {
        
        String query = "SELECT speetid, username, message, timestamp FROM Speets WHERE username = ? UNION " +
                       "SELECT speetid, username, message, timestamp FROM Speets WHERE message LIKE ? UNION " +
                       "SELECT speetid, username, message, timestamp from Speets WHERE username IN " +
                       "(SELECT follows FROM Followers WHERE username = ?) ORDER BY timestamp DESC LIMIT 0, 20";
        
        try {
            Connection conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, username);
            statement.setString(2, "%@" + username + "%");
            statement.setString(3, username);
            ResultSet rs = statement.executeQuery();
            
            ArrayList<Speet> speets = new ArrayList<Speet>();
             
            while (rs.next()) {
               Speet speet = new Speet();
               speet.setSpeetId(rs.getInt(1));
               speet.setUsername(rs.getString(2));
               speet.setMessage(rs.getString(3));
               speet.setTimeStamp(rs.getString(4));
               speets.add(speet); 
            }

            conn.close();
            
            return speets;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Error in the SQL: " + query, ex);
        }
        
        return null;
    }
    
    /**
     * Returns the all speets written by a given user.
     * 
     * @param username
     * @return 
     */
    public ArrayList<Speet> getSpeets(String username) {
        
        String query = "SELECT speetid, message, timestamp FROM Speets WHERE username = ? " +
                       "ORDER BY timestamp DESC";
        
        try {
            Connection conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, username);
            ResultSet rs = statement.executeQuery();
            
            ArrayList<Speet> speets = new ArrayList<Speet>();
             
            while (rs.next()) {
               Speet speet = new Speet();
               speet.setUsername(username);
               speet.setSpeetId(rs.getInt(1));
               speet.setMessage(rs.getString(2));
               speet.setTimeStamp(rs.getString(3));
               speets.add(speet); 
            }

            conn.close();
            
            return speets;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Error in the SQL: " + query, ex);
        }
        
        return null;
    }
    
    /**
     * Inserts the Spitter user into the database. Returns true if the insert
     * succeeds, false otherwise (e.g., if the username is not unique).
     * 
     * @param user
     * @return 
     */
    public boolean insertUser(SpitterUser user) {
        
        String sql = "INSERT INTO SpitterUsers (username, password, " +
                    "about) VALUES (?, ?, ?)";
        try {  
            System.out.println("insertUser " + user.toString());
            
            Connection conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, user.getUsername());
            statement.setString(2, user.getPassword());
            statement.setString(3, user.getAbout());
            statement.execute(); 
            conn.close();
            
            return true;
        } catch (MySQLIntegrityConstraintViolationException ex) {
            System.out.println("insertUser: Duplicate username '" + 
                    user.getUsername() + "' being used.");            
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Error in SQL: " + sql, ex);
        }
        
        return false;
    }
    
    /**
     * A given user unfollows another user.
     * 
     * @param username
     * @param follows
     * @return 
     */
    public boolean unfollow(String username, String follows) {
                
        String sql = "DELETE FROM Followers WHERE username = ? AND follows = ?";        
        try {       
            Connection conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(sql);   
            statement.setString(1, username);
            statement.setString(2, follows);
            statement.execute(); 
            conn.close();
            
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Bad SQL: " + sql, ex);
        }
        
        return false;
    }
    
    /**
     * A given user follows another user.
     * 
     * @param username
     * @param follow
     * @return 
     */
     public boolean follow(String username, String follow) {
                
        String sql = "INSERT INTO Followers VALUES (?, ?)";        
        try {       
            Connection conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(sql);   
            statement.setString(1, username);
            statement.setString(2, follow);
            statement.execute(); 
            conn.close();
            
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Bad SQL: " + sql, ex);
        }
        
        return false;
    }
    
    /**
     * Updates the user's information in the database. Return true if
     * the update succeeded, false otherwise (e.g., the username was not found).
     * 
     * @param user
     * @return 
     */
    public boolean updateUser(SpitterUser user) {
                
        String sql = "UPDATE SpitterUsers SET password=?, " +
                    "about=? WHERE username=?";        
        try {       
            Connection conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(sql);            
            statement.setString(1, user.getPassword());
            statement.setString(2, user.getAbout());
            statement.setString(3, user.getUsername());
            statement.execute(); 
            conn.close();
            
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Bad SQL: " + sql, ex);
        }
        
        return false;
    }
    
   /**
     * Returns all the Spitter users who are following a given username.
     * The list will be empty if the username is not in the database. 
     * 
     * @param username
     * @return 
     */
    public ArrayList<SpitterUser> getFollowers(String username){
        
        ArrayList<SpitterUser> followerList = new ArrayList<SpitterUser>();
        
        Connection conn = null;
        String query = null;
        
        try {
            query = "SELECT username, about from SpitterUsers WHERE username IN " +
                    "(SELECT username FROM Followers WHERE follows = ?)";
            
            
            conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, username);
            ResultSet rs = statement.executeQuery();
              
            HashMap<Integer, String> hash = new HashMap<Integer, String>();
            ArrayList<SpitterUser> followingList = getFollowing(username);
            int count = 0;
            for (SpitterUser user : followingList)
                hash.put(count++, user.getUsername());
            
            while (rs.next()) {
                SpitterUser user = new SpitterUser();
                user.setUsername(rs.getString(1));
                user.setAbout(rs.getString(2));
                if (hash.containsValue(user.getUsername()))
                     user.setFollowing(true);
                else
                     user.setFollowing(false);
               
                followerList.add(user);
            }

            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Bad query: " + query, ex);
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            }
        }        
        return followerList;
    }
    
    /**
     * Returns all the Spitter users who are being followed by the given username.
     * The list will be empty if the username is not in the database.
     * 
     * @param username
     * @return 
     */
    public ArrayList<SpitterUser> getFollowing(String username) {
        
        ArrayList<SpitterUser> followingList = new ArrayList<SpitterUser>(); 
        
        Connection conn = null;
        String query = null;
        
        try {
            query = "SELECT username, about from SpitterUsers WHERE username IN " +
                    "(SELECT follows FROM Followers WHERE username = ?)";

            conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, username);
            ResultSet rs = statement.executeQuery();

            while (rs.next()) {
                SpitterUser user = new SpitterUser();
                user.setUsername(rs.getString(1));
                user.setAbout(rs.getString(2));
                user.setFollowing(true);
                followingList.add(user);
            }

            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Bad query: " + query, ex);
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            }
        }        
        
        return followingList;
    }
    
    /**
     * Returns the number of followers an user has.
     * 
     * @param username
     * @return 
     */
     public int getFollowersTotal(String username) {
        
        int total = -1;
        
        Connection conn = null;
        String query = null;
        
        try {
            query = "SELECT count(*) from SpitterUsers WHERE username IN " +
                    "(SELECT username FROM Followers WHERE follows = ?)";

            conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, username);
            ResultSet rs = statement.executeQuery();

            while (rs.next()) {
                total = rs.getInt(1);
            }

            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Bad query: " + query, ex);
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            }
        }        
        
        return total;
    }
    
     /**
      * Returns the number of users following a given user.
      * 
      * @param username
      * @return 
      */
    public int getFollowingTotal(String username) {
        
        int total = -1;
        
        Connection conn = null;
        String query = null;
        
        try {
            query = "SELECT count(*) from SpitterUsers WHERE username IN " +
                    "(SELECT follows FROM Followers WHERE username = ?)";

            conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, username);
            ResultSet rs = statement.executeQuery();

            while (rs.next()) {
                total = rs.getInt(1);
            }

            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Bad query: " + query, ex);
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            }
        }        
        
        return total;
    }
    
    /**
     * returns the number of speets of a given user.
     * 
     * @param username
     * @return 
     */
     public int getSpeetsTotal(String username) {
        
       int total = -1;
        
        Connection conn = null;
        String query = null;
        
        try {
            query = "SELECT count(*) FROM Speets WHERE username = ?";

            conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, username);
            ResultSet rs = statement.executeQuery();

            while (rs.next()) {
                total = rs.getInt(1);
            }

            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Bad query: " + query, ex);
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            }
        }        
        
        return total;
    }
     
    
    /**
      * Search the database for a any username or about matching string.
      * 
      * @param search
      * @return 
      */
    public ArrayList<SpitterUser> searchForUser(String search, String username) {
        
        ArrayList<SpitterUser> results = new ArrayList<SpitterUser>(); 
        
        Connection conn = null;
        String query = null;
        Pattern pattern = null;
        Matcher matcher;
        
        try {
            query = "SELECT username, about from SpitterUsers WHERE username LIKE " +
                    "? OR about LIKE ?";

            conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, "%" + search + "%");
            statement.setString(2, "%" + search + "%");
            ResultSet rs = statement.executeQuery();

            HashMap<Integer, String> hash = new HashMap<Integer, String>();
            ArrayList<SpitterUser> followingList;
            int count;
            while (rs.next()) {
                if (!username.equalsIgnoreCase(rs.getString(1))){
                    
                    SpitterUser user = new SpitterUser();
                    user.setUsername(rs.getString(1));
                    
                    pattern = Pattern.compile("(" + search + ")", Pattern.CASE_INSENSITIVE); 
                    matcher = pattern.matcher(rs.getString(2)); 
                    String about = matcher.replaceAll("<b>$1</b>");  
                        
                    user.setAbout(about);

                    followingList = getFollowing(username);
                    count = 0;
                    for (SpitterUser u : followingList)
                        hash.put(count++, u.getUsername());

                    if (hash.containsValue(user.getUsername()))
                         user.setFollowing(true);
                    else
                         user.setFollowing(false);

                    results.add(user);
                    hash.clear();
                    followingList.clear();
                }
            }

            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Bad query: " + query, ex);
        } finally {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            }
        }        
        
        return results;
     }
    
    /**
     * Search the database for any given keyword found in the speets table
     * 
     * @param search
     * @return 
     */
    public ArrayList<Speet> searchForSpeets(String search) {
        
        String query = "SELECT speetid, username, message, timestamp FROM Speets " +
                       "WHERE message LIKE ? ORDER BY timestamp DESC";
        
        Pattern pattern = null;
        Matcher matcher = null;
        
        try {
            Connection conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, "%" + search + "%");
            ResultSet rs = statement.executeQuery();
            
            ArrayList<Speet> speets = new ArrayList<Speet>();
             
            while (rs.next()) {
               Speet speet = new Speet();
               speet.setSpeetId(rs.getInt(1));
               speet.setUsername(rs.getString(2));
               
               pattern = Pattern.compile("(" + search + ")", Pattern.CASE_INSENSITIVE); 
               matcher = pattern.matcher(rs.getString(3)); 
               String message = matcher.replaceAll("<b>$1</b>"); 
               
               speet.setMessage(message);
               speet.setTimeStamp(rs.getString(4));
               speets.add(speet); 
            }

            conn.close();
            
            return speets;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Error in the SQL: " + query, ex);
        }
        
        return null;
    }
    
    /**
     * Insert a speet into the database.
     * 
     * @param speet
     * @return 
     */
    public boolean insertSpeet(Speet speet)
    {
         String sql = "INSERT INTO Speets (username, message, " +
                    "timestamp) VALUES (?, ?, now())";
        try {  
            System.out.println("insertSpeet " + speet.toString());
            
            Connection conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, speet.getUsername());
            statement.setString(2, speet.getMessage());
            statement.execute(); 
            conn.close();
            
            return true;
        } catch (MySQLIntegrityConstraintViolationException ex) {
            System.out.println("insertUser: Duplicate username '" + 
                    speet.getUsername() + "' being used.");            
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, 
                    "Error in SQL: " + sql, ex);
        }
        
        return false;
    }
}
